#!/usr/bin/perl 

use DBI;
use Getopt::Long;

my %POSTGRES_RESERVED_WORDS=("user"=>1,"position"=>1);

my %WAND_HASH_TYPE=(
"integer(\\([\\\d]*\\))?"=>"integer",
"tinyint(\\([\\\d]*\\))?"=>"smallint",
"smallint(\\([\\\d]*\\))?"=>"smallint",
"meduimint(\\([\\\d]*\\))?"=>"integer",
"bigint(\\([\\\d]*\\))?"=>"int8",
"int(\\([\\\d]*\\))?"=>"integer",
"double\\([\\\d]*,[\\\d]*\\)"=>"double precision",
"double precision\\([\\\d]*,[\\\d]*\\)"=>"double precision",
"real\\([\\\d]*,[\\\d]*\\)"=>"real",
"decimal"=>"decimal",
"numeric"=>"numeric",
"varchar(\\([\\\d]*\\))?"=>"varchar",
"char(\\([\\\d]*\\))( binary)?"=>"varchar",
"datetime"=>"timestamp",
"date"=>"date",
"timestamp\\([\\\d]*\\)"=>"timestamp",
"time"=>"time",
"bit"=>"bit",
"bool"=>"boolean",
"mediumtext"=>"text",
"longtext"=>"text",
"tinytext"=>"text",
"text"=>"text",
"mediumblob"=>"OID",
"longblob"=>"OID",
"tinyblob"=>"OID"
);

{
	GetOptions(
		"help"=>\$Param{'help'},
		"file|f=s"=>\$Param{'file'},
		"database|d=s"=>\$Param{'database'},
		"table|t=s"=>\$Param{'table'},
		"user|u=s"=>\$Param{'user'},
		"pass|p:s"=>\$Param{'pass'},
		"host|h=s"=>\$Param{'host'},
		"force"=>\$Param{'force'},
		"drop"=>\$Param{'drop'},
		"data"=>\$Param{'data'}
	);

	$database=$Param{'database'};
	$table=$Param{'table'};
	$host=($Param{'host'} ne '' ? $Param{'host'} : "localhost");
	$user=$Param{'user'};
	$pass=$Param{'pass'};
	if ($user && $database) {
		$Param{'mysqldb'}=1;
	}
	if ((!($Param{'mysqldb'} || $Param{'file'})) || $Param{'help'}) {
		usage();
	}
	
	if ($Param{'mysqldb'}) {
		$pass=get_pass($Param{"pass"});
		$dbh=DBI->connect("DBI:mysql:$database:$host","$user","$pass");
		@db_array=db_read();
		print join("\n\n",@db_array);
	}
	else {
		%db_array=file_read();
		foreach (keys %db_array) {
			print $_."\n",$db_array{$_}."\n";
		}
	}
}

sub get_pass {
	if (!($_[0])) {
		print STDERR "Enter password: ";
		system "stty -echo";
		chomp($pass=<STDIN>);
		system "stty echo";
		print STDERR "\n\n";
		return $pass;
	}
	return $_[0];
}

sub db_read {
	my $TABLE_NAME=''; my %tables; my @RET_ARRAY=();
	
	if (!($Param{"table"})) {
		my $sql="show tables from $database";
		my $sth=$dbh->prepare($sql);
		$sth->execute;
		while (@tt=$sth->fetchrow_array) {
			$TABLE_NAME=verify_it($tt[0],"tablename");
			$tables{$tt[0]}=$TABLE_NAME;
		}    
	}
	else {
			$TABLE_NAME=verify_it($table,"tablename");
		   	$tables{$table}=$TABLE_NAME;
	}

	foreach $TABLE (keys %tables) {
		my $str='';
		my @fields=();
		my @tablekeys=();
		my $sql="describe $TABLE";
		my $sth=$dbh->prepare($sql);
		$sth->execute;
		if ($Param{'drop'}) {
			$str="DROP TABLE \"$tables{$TABLE}\";";
		}
		$str.="\n\nCREATE TABLE \"$tables{$TABLE}\" (\n";
		while ($foo=$sth->fetchrow_hashref()) {
			push @fields,detypize($foo);
			if ($foo->{'Key'}=~/^PRI$/) {
			    push @tablekeys,$foo->{'Field'};
			}
		}	
		$str.=join(",\n",@fields);
		if (@tablekeys) {
			$str.=",\n"."PRIMARY KEY (".join(", ",@tablekeys).")\n";
		}
		$str.=");\n";
		if ($Param{'data'}) {
        		$str.=data_from_db($TABLE,$tables{$TABLE});
		}
		push @RET_ARRAY,$str;
	}
	return @RET_ARRAY;
}

sub data_from_db {
		my $data='';
		my $sql="select * from $_[0]";
		my $sth=$dbh->prepare($sql);
		$sth->execute;
		while ($foo=$sth->fetchrow_hashref()) {
			foreach (values %{$foo}) {
				$_=~s/\"|\'/\\$&/ig;
			}
			$data.="INSERT INTO $_[1] values(\'".join("\',\'",values %{$foo})."\');\n";
		}
		return $data;

}

sub file_read {
	my $SQL_1=''; my @fields=(); my $PRIM_KEY=''; my $TABLE=''; my $REC_SET='';

	open(FILE,$Param{'file'});
	while (<FILE>) {
		next if /^#/;
		next if /^\)[\s|\t]+TYPE/;
		$_=~s/^[\s|\t]+//;
		($_=~/(.[^\#]*)(\#)/ig ? $SQL_1=$1 : $SQL_1=$_);
		if ($SQL_1=~/(^CREATE)([\s|\t]+)(TABLE)([\s|\t]+)(.[^\(]*)(\()/ig) {
			if ($TABLE_NAME) {
				if ($Param{'drop'}) {
				    $str_tmp="DROP TABLE \"$TABLE_NAME\";";
				}
				$str_tmp.="\n\nCREATE TABLE \"".$TABLE_NAME."\"(\n".join(",\n",@fields).");\n";
				$RET_ARRAY{$str_tmp}=$table_data; 
				@fields=(); $table_data=''; %str=(); $str_tmp='';
			}
			$TABLE_NAME=$5;
			$TABLE_NAME=~s/[\s|\t]+//ig;
			$TABLE_NAME=verify_it($TABLE_NAME,"tablename");
		} 
		else {
			if ($SQL_1=~/(PRIMARY)([\s|\t]+)(KEY)([\s|\t]+)(\()(.[^\)]*)(\))/ig) {
				my $TABLE_KEYS=$6;
				my @temp_keys=();
					if ($TABLE_KEYS=~/\,/) {
					    @temp_keys=split(/\,/,$TABLE_KEYS);
					}
					else {
					    push @temp_keys,$TABLE_KEYS;				        
					}
				my @ret_keys=make_keys_fromfile(\@temp_keys);
				if ($#ret_keys>-1) {
				    $PRIM_KEY="PRIMARY KEY (".join(", ",@ret_keys).")\n";
				    push @fields,$PRIM_KEY;
				}
			}
			else {
				if ($SQL_1!~/INSERT([\s|\t]+)INTO/ig) {
					$SQL_1=~s/binary//ig;
					$REC_SET->{'Field'}='';	$REC_SET->{'Type'}=''; $REC_SET->{'Default'}=''; $REC_SET->{'Null'}='';	my $b='';
					($REC_SET->{'Field'},$REC_SET->{'Type'},$b)=split(/\s+/,$SQL_1,3);
					if (!$b) {
					    $REC_SET->{'Type'}=~s/\,//ig;
					}
					if ($b=~/DEFAULT[\s|\t]+NULL/ig) {
						$REC_SET->{'Null'}="YES";
					}
					if ($b=~/(DEFAULT)([\s|\t]+)(\'|\")(.[^\'|\"]*)(\'|\")/ig) {
						$REC_SET->{'Default'}=$4;
					}
					if ($REC_SET->{'Field'} eq 'KEY') {
						print STDERR "KEY: $_\n";
						next;
					}
					if ($b=~/(NOT[\s|\t]+NULL)/ig) {
						$REC_SET->{'Null'}="NO";
					}
					if ($REC_SET->{'Field'} && $REC_SET->{'Type'}) {
							push @fields,detypize($REC_SET);
					}		
				}
				else {
					if ($Param{'data'}) {
					    $SQL_1=~s/(INSERT[\s|\t]+INTO)([\s|\t]+)(.[^\s]*)(.*)/$1 $TABLE_NAME $4/ig;
					    $table_data.=$SQL_1;
					}
				}
			}
		}
		
	}
	close(FILE);
	$str_tmp='';
	if ($Param{'drop'}) {
		    $str_tmp="DROP TABLE \"$TABLE_NAME\";";
	}
	$str_tmp.="\n\nCREATE TABLE \"".$TABLE_NAME."\"(\n".join(",\n",@fields).");\n";
	$str{$str_tmp}=$table_data;
	$RET_ARRAY{$str_tmp}=$table_data;
	return %RET_ARRAY;
}

sub verify_it {
	my $ret_str='';
	foreach  (keys %POSTGRES_RESERVED_WORDS) {
		if ($_[0]=~/^$_$/i) {
			print STDERR "--ERROR: \n";
			if ($Param{'force'}) {
				print STDERR "--Reserved word in using by $_[1]: $_[0]\n--Setting to ".$_[0]."_1\n";
				$ret_str=$_[0]."_1";
				return $ret_str;
			}
			else {
				die "Reserved word in using by $_[1]: $_[0]\nTry to force\n";
			}
		}
	}
	return $_[0];
}

sub make_keys_fromfile {
    my $temp_keys=$_[0];
    my @ret_keys=();
    foreach (@{$temp_keys}) {
	push @ret_keys,verify_it($_,"keyname");
    }
    return @ret_keys;
}

sub detypize {
	my $dataset=$_[0];
	my $bla=$dataset->{Type};
	foreach (keys %WAND_HASH_TYPE) {
		$dataset->{Field}=verify_it($dataset->{Field},"fieldname");
		if ($dataset->{Type}=~s/^$_$/$WAND_HASH_TYPE{$_}/ig) {
			my $C_L=$1;
			if ($dataset->{Type}=~/char$/ig)  { $dataset->{Type}.=$C_L; }
			return $dataset->{Field}." ".$dataset->{Type}." ".($dataset->{Null} eq 'YES' ? "DEFAULT NULL " : ($dataset->{Default} ne '' ? " DEFAULT \'".$dataset->{Default}."\'" : " NOT NULL "));
		}
		if ($dataset->{Type}=~/(enum\()(.[^\)]*)(\))/) {
			my @values=split(/\,/,$2);
			my $type_length=check_max_length(\@values);
			my $ruleset=create_rules(\@values,$dataset->{Field});
			return $dataset->{Field}." varchar(".$type_length.") ".($dataset->{Default} ? "DEFAULT '".lc($dataset->{Default})."' " : " ").$ruleset;
		}
#********************DATATYPE SET: CREATE AS ARRAY --> FIX ME!!!
		if ($dataset->{Type}=~/(set\()(.[^\)]*)(\))/) {
			my @values=split(/\,/,$2);
			return $dataset->{Field}." varchar[".$#values."]";
		}
#*******************************************************
		if ($dataset->{Type}=~/(year\()([\d])(\))/) {
			my $ruleset=create_rules_year($2,$dataset->{Field});
			return $dataset->{Field}." integer ".$ruleset;
		}
	}
	if ($Param{'force'}) {
	    return "/*".$dataset->{Field}." ".$dataset->{Type}." ".($dataset->{Null} eq 'YES' ? "DEFAULT NULL " : ($dataset->{Default} ne '' ? " DEFAULT \'".$dataset->{Default}."\'" : " NOT NULL "))." FIX ME!*/";
	}
	else {
	    die "Unknown column type $dataset->{Type}\n";
	}
}


sub check_max_length {
	my $max=0;
	foreach (@{$_[0]}) {
		~s/\'|\"//ig;
		$max=($max > length($_) ? $max : length($_));
	}
	return $max;
}


sub create_rules {
	return $rules="CHECK (".join(" OR ",map("lower(".$_[1].")=\'".lc($_)."\'",@{$_[0]})).")";
}

sub create_rules_year {
	my $rules="CHECK (";
	my $FIELD=$_[1];
	if ($_[0]==2) {
		$rules.=$FIELD.">1970 AND ".$FIELD."< 2069 ";
	}
	else {
		$rules.="(".$FIELD."=0000) OR (".$FIELD.">1900 AND ".$FIELD."<2155) ";
	}
	$rules.=")";
	return $rules;
}

sub usage {
    print "Usage: $0 [-h host -u user -p pass -d database [-t table]] [-f file] OPTIONS \n\n";
    printf(" %s\t\t\t%s\n\n","--help","print this message");
    printf(" %s\t\t%s\n","-u, --user","username for logging in to the MySQL-DB");
    printf(" %s\t\t%s\n","-p, --password","validate password for user");
    printf(" %s\t\t%s\n","-h, --host","name or IP of the host (default localhost)");
    printf(" %s\t\t%s\n","-d, --database","database to convert");
    printf(" %s\t\t%s\n","-t, --table","table to convert");
    printf(" %s\t\t%s\n\n","-f, --file","mysqldump-file to convert");
    printf(" %s\t\t%s\n","--force","force mode (see README)");
    printf(" %s\t\t\t%s\n","--data","with data (see README)");
    printf(" %s\t\t\t%s\n\n","--drop","add 'drop table' before each create statement");

    exit;
}
